package com.dao.impl;

import com.dao.opMenu;
import com.entity.Menus;
import com.entity.OrderStatus;
import com.entity.Trolley;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

import static com.util.Vessel.*;

public class opMenuImpl implements opMenu {

    //查看菜单
    @Override
    public List<Menus> vsMenus(){
        String sql = "select *\n" +
                "from menus\n" +
                "where mId > ?";
        Object[] object = new Object[1];
        object[0] = 0;
        ResultSet resultSet = orderCRUD.printAll(sql,object);
        List<Menus> menuList = new ArrayList<>();
        try{
            while(resultSet.next()){
                Menus menus = new Menus();
                menus.setmId(resultSet.getInt("mId"));
                menus.setmName(resultSet.getString("mName"));
                menus.setePrice(resultSet.getDouble("mPrice"));
                menuList.add(menus);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return menuList;


    }

    //查看餐袋
    @Override
    public List<Trolley> vsMealBag(int cId,int tId) {
        String sql = "SELECT me.mId,me.mName,SUM(tro.mNumber) Number,me.mPrice,tro.tId,SUM(me.mPrice * mNumber) aPrice,tro.orderStatus\n" +
                "FROM trolley AS tro\n" +
                "LEFT JOIN menus AS me ON tro.mId = me.mId\n" +
                "WHERE cId = ? AND tro.tStatus = false AND tId = ?\n" +
                "GROUP BY me.mId,mName,mPrice,tro.tId,tro.orderStatus";
        Object[] objects = new Object[2];
        objects[0] = cId;
        objects[1] = tId;
        ResultSet resultSet = orderCRUD.printAll(sql,objects);
        List<Trolley> TrolleyList = new ArrayList<>();
        try{
            while(resultSet.next()){
                Trolley trolley = new Trolley();
                trolley.setmId(resultSet.getInt("me.mId"));
                trolley.setName(resultSet.getString("me.mName"));
                trolley.setNumber(resultSet.getInt("Number"));
                trolley.setPrice(resultSet.getDouble("me.mPrice"));
                trolley.setaPrice(resultSet.getDouble("aPrice"));
                trolley.settId(resultSet.getInt("tro.tId"));
                trolley.setOrderStatus(resultSet.getString("tro.orderStatus"));
                TrolleyList.add(trolley);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return TrolleyList;
    }

    //管理餐袋
    @Override
    public int magMealBag(int cId,int mId,int mNumber,int tId) {
        int num = 0;
        if (mNumber == 0){
            String sql = "DELETE FROM trolley\n" +
                    "WHERE cId = ?\n" +
                    "AND mId = ?" +
                    "AND tId = ?";
            Object[] objects = new Object[3];
            objects[0] = cId;
            objects[1] = mId;
            objects[2] = tId;
            num = orderCRUD.update(sql,objects);
            if (num > 0){
                System.out.println("操作成功！");
            }
        }else if (mNumber > 0){
            String sql = "UPDATE trolley SET mNumber = ? WHERE mId = ?";
            Object[] objects = new Object[2];
            objects[0] = mNumber;
            objects[1] = mId;
            num = orderCRUD.update(sql,objects);
            if (num > 0){
                System.out.println("操作成功！");
            }
            /*String sql = "SET @diff = ? - (SELECT SUM(mNumber) FROM trolley WHERE cId = ? AND mId = ?);\n" +
                    "SET @count = (SELECT COUNT(*) FROM trolley WHERE cId = ? AND mId = ?);\n" +
                    "SET @increment = IF(@count > 0, @diff / @count, 0);\n" +
                    "\n" +
                    "UPDATE trolley\n" +
                    "SET mNumber = mNumber + @increment\n" +
                    "WHERE cId = ? AND mId = ?;";
                    Object[] objects = new Object[7];
                    objects[0] = mNumber;
                    objects[1] = cId;
                    objects[2] = mId;
                    objects[3] = cId;
                    objects[4] = mId;
                    objects[5] = cId;
                    objects[6] = mId;*/
            /*String sql = "UPDATE trolley t1\n" +
                    "JOIN (\n" +
                    "    SELECT\n" +
                    "        SUM(mNumber) AS total_mNumber,\n" +
                    "        COUNT(*) AS count_records\n" +
                    "    FROM trolley\n" +
                    "    WHERE cId = ? AND mId = ?\n" +
                    ") t2\n" +
                    "ON 1 = 1 \n" +
                    "SET t1.mNumber = t1.mNumber +\n" +
                    "    CASE\n" +
                    "        WHEN t2.count_records > 0 THEN\n" +
                    "            (? - t2.total_mNumber) / t2.count_records\n" +
                    "        ELSE\n" +
                    "            0\n" +
                    "    END\n" +
                    "WHERE t1.cId = ? AND t1.mId = ?;";
            Object[] objects = new Object[5];
            objects[0] = cId;
            objects[1] = mId;
            objects[2] = mNumber;
            objects[3] = cId;
            objects[4] = mId;
            num = orderCRUD.update(sql,objects);*/
        }else {
            System.out.println("输入错误！");
        }
        return num;
    }

    //检测菜单中是否存在该菜
    @Override
    public int detect(int mId) {
        String sql = "SELECT mId FROM menus WHERE mId = ?";
        Object[] objects = new Object[1];
        objects[0] = mId;
        ResultSet resultSet = orderCRUD.printAll(sql,objects);
        int id = 0;
        try{
            while(resultSet.next()){
                id = resultSet.getInt("mId");
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return id;
    }

    //确认加入
    @Override
    public boolean register(int cId, int tId, int mId, int mNumber) {
        String sql2 = "SELECT mId FROM trolley WHERE mId = ? AND cId = ?";
        Object[] objects = new Object[2];
        objects[0] = mId;
        objects[1] = cId;
        ResultSet resultSet = orderCRUD.printAll(sql2,objects);
        int id = 0;
        try{
            while(resultSet.next()){
                id = resultSet.getInt("mId");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        if (id > 0){
            String sql3 = "UPDATE trolley SET mNumber = mNumber + ? WHERE mId = ?";
            Object[] objects1 = new Object[2];
            objects1[0] = mNumber;
            objects1[1] = mId;
            orderCRUD.update(sql3,objects1);
        }else {
            try{
                String sql1 = "INSERT INTO trolley(mId,mNumber,cId,tId) VALUE(?,?,?,?);";
                Object[] objects1 = new Object[4];
                objects1[0] = mId;
                objects1[1] = mNumber;
                objects1[2] = cId;
                objects1[3] = tId;
                int num1 = orderCRUD.update(sql1,objects1);
                if (num1 > 0){
                    System.out.println("加入购物车成功");
                }
            }catch(Exception e){
                e.printStackTrace();
            }
        }
        return false;
    }

    //检测已勾选菜品是否为空
    @Override
    public boolean isNull(int cId, int tId) {
        boolean isFlag = false;
        String sql = "SELECT troId FROM trolley WHERE cId = ? AND tId = ? AND orderStatus = '已勾选'";
        Object[] objects = new Object[2];
        objects[0] = cId;
        objects[1] = tId;
        ResultSet resultSet = orderCRUD.printAll(sql,objects);
        int id = 0;
        try{
            while(resultSet.next()){
                id = resultSet.getInt("troId");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        if (id > 0){
            isFlag = true;
        }
        return isFlag;
    }

    //是否有菜品选中
    @Override
    public boolean selectNull(int cId, int tId) {
        boolean isFlag = false;
        String sql = "SELECT troId FROM trolley WHERE cId = ? AND tId = ?";
        Object[] objects = new Object[2];
        objects[0] = cId;
        objects[1] = tId;
        ResultSet resultSet = orderCRUD.printAll(sql,objects);
        int id = 0;
        try{
            while(resultSet.next()){
                id = resultSet.getInt("troId");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        if (id > 0){
            isFlag = true;
        }
        return isFlag;
    }

    //清除购物车
    @Override
    public boolean cleanAll(int cId, int tId) {
        int num = 0;
        boolean isFlag = false;
        String sql = "DELETE trolley WHERE cId = ? AND tId = ?";
        Object[] objects = new Object[2];
        objects[0] = cId;
        objects[1] = tId;
        num = orderCRUD.update(sql,objects);
        if (num > 0){
            isFlag = true;
        }
        return isFlag;
    }

    //推荐菜
    @Override
    public List<Menus> recommendMenus() {
        String sql = "SELECT mName,mPrice\n" +
                "FROM menus\n" +
                "WHERE mPrice < ?";
        Object[] objects = new Object[1];
        objects[0] = 50;
        ResultSet resultSet = orderCRUD.printAll(sql,objects);
        List<Menus> menusList = new ArrayList<>();
        try{
            while(resultSet.next()){
                Menus menu = new Menus();
                menu.setmName(resultSet.getString("mName"));
                menu.setePrice(resultSet.getDouble("mPrice"));
                menusList.add(menu);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return menusList;
    }

}
